package com.tbit.uqbike.client.util;

import com.alibaba.fastjson.JSONObject;
import com.tbit.uqbike.client.pojo.BleExcelPojo;
import com.tbit.uqbike.client.pojo.User;
import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.MultiThreadedHttpConnectionManager;
import org.apache.commons.httpclient.methods.GetMethod;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: chen
 * @Description: excel工具类
 * @Date: 创建于 8:58 2020/7/15
 */
public class ExcelUtil {

    private final static Logger log = LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);
    private static HttpClient client = null;

    /**
     * 读取excel数据，匹配文本
     *
     * @param filePath excel文件路径
     * @param key      匹配的键
     * @return
     */
    public static BleExcelPojo read(String filePath, String key) {
        BleExcelPojo excelPojo = new BleExcelPojo();
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(0);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }

                if (cell != null && cell.getStringCellValue().toUpperCase().equals(key.toUpperCase())) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    excelPojo.setCell1(row.getCell(0).getStringCellValue());
                    excelPojo.setCell2(row.getCell(1).getStringCellValue());
                    excelPojo.setCell3(row.getCell(2).getStringCellValue());
                    excelPojo.setCell4(row.getCell(3).getStringCellValue());
                    excelPojo.setCell5(row.getCell(4).getStringCellValue());
                    return excelPojo;
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    public static BleExcelPojo readByMachineNO(String filePath, String machineNO) {
        BleExcelPojo excelPojo = new BleExcelPojo();
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(0);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }

                if (cell != null && cell.getStringCellValue().toUpperCase().equals(machineNO.toUpperCase())) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    excelPojo.setCell1(row.getCell(0).getStringCellValue());
                    excelPojo.setCell2(row.getCell(1).getStringCellValue());
                    excelPojo.setCell3(row.getCell(2).getStringCellValue());
                    excelPojo.setCell4(row.getCell(3).getStringCellValue());
                    excelPojo.setCell5(row.getCell(4).getStringCellValue());
                    return excelPojo;
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    /**
     * 通过文件查询设备编号
     *
     * @param filePath
     * @param key
     * @return
     */
    public static String getMachineNO(String filePath, String key) {
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(1);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }

                if (cell != null && cell.getStringCellValue().toUpperCase().contains(key.toUpperCase())) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    String machineNO = row.getCell(0).getStringCellValue();
                    if (machineNO != null) {
                        machineNO = machineNO.substring(machineNO.indexOf("=") + 1);
                    }
                    return machineNO;
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    public static Map<String, JSONObject> read(String filePath) {
        Map<String, JSONObject> map = new HashMap<>();
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("mno", row.getCell(0).getStringCellValue());
                jsonObject.put("addDt", row.getCell(1).getDateCellValue());
                jsonObject.put("retDt", row.getCell(3) != null ? row.getCell(3).getDateCellValue() : null);
                map.put(row.getCell(0).getStringCellValue(), jsonObject);
            }
            return map;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    public static List<JSONObject> read1(String filePath) {
        List<JSONObject> objects = new ArrayList<>();
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 2; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("accountId", row.getCell(1).getStringCellValue());
                    jsonObject.put("lon", row.getCell(17).getStringCellValue());
                    jsonObject.put("lat", row.getCell(18).getStringCellValue());
                    objects.add(jsonObject);
                }
            }
            return objects;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    /**
     * 导入用户数据
     *
     * @param filePath
     * @return
     */
    public static List<User> insertOtherUser(String filePath) {
        List<User> objects = new ArrayList<>();
        InputStream inputStream = null;
        BufferedInputStream stream = null;
        try {
            inputStream = new FileInputStream(filePath);
            stream = new BufferedInputStream(inputStream);
            Workbook wb = null;
            if (POIFSFileSystem.hasPOIFSHeader(stream)) {
                wb = new HSSFWorkbook(stream);
            } else if (POIXMLDocument.hasOOXMLHeader(stream)) {
                wb = new XSSFWorkbook(stream);
            } else {
                return null;
            }
            /**获取第一个sheet工作蒲*/
            Sheet sheet = wb.getSheetAt(0);
            /**获取最大行数*/
            int count = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < count; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(2).setCellType(Cell.CELL_TYPE_NUMERIC);
                    User user = new User();
                    user.setPhone(row.getCell(0).getStringCellValue());
                    user.setSex(row.getCell(1).getStringCellValue() == "男" ? "1" : row.getCell(1).getStringCellValue() == "男" ? "0" : "未知");
                    user.setMoney(row.getCell(2).getNumericCellValue());
                    user.setNameAuth(false);
                    if (row.getCell(3) != null) {
                        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                        user.setName(row.getCell(3).getStringCellValue());
                        user.setNameAuth(true);
                    }
                    if (row.getCell(4) != null) {
                        row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                        user.setIdNO(row.getCell(4).getStringCellValue());
                    }
                    objects.add(user);
                }
            }
            return objects;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                stream.close();
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
        return null;
    }

    public static void add() throws FileNotFoundException {
        List<User> users = insertOtherUser("C:\\Users\\Administrator\\Desktop\\1111.xlsx");
        int i = users.size() % 500;
        int m = users.size() / 500;
        List<Object> objectList = new ArrayList<>();
        for (int n = 0; n < m; n++) {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("insert into user(accountId,country,phone,sex,money,name,idNO,nameAuth,remark) values ");
            String tmp = "(11389,86,";
            List<User> list = users.subList(n * 500, (n + 1) * 500);
            for (User user : list) {
                if (user.getName() == null) {
                    user.setName("");
                    user.setIdNO("");
                } else {
                    user.setIdNO(user.getIdNO().substring(1));
                }
                stringBuffer.append(tmp + user.getPhone() + "','" + user.getSex() + "'," + user.getMoney() + ",'" + user.getName() + "','" + user.getIdNO() + "'," + (user.getNameAuth() == false ? 0 : 1) + ",'手动统一导入'),");
            }
            stringBuffer.delete(stringBuffer.length() - 1, stringBuffer.length());
            stringBuffer.append(";");
            objectList.add(stringBuffer.toString());
        }
        if (i > 0) {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("insert into user(accountId,country,phone,sex,money,name,idNO,nameAuth,remark) values ");
            String tmp = "(11389,86,";
            List<User> list = users.subList(m * 500, users.size());
            for (User user : list) {
                if (user.getName() == null) {
                    user.setName("");
                    user.setIdNO("");
                } else {
                    user.setIdNO(user.getIdNO().substring(1));
                }
                stringBuffer.append(tmp + user.getPhone() + "','" + user.getSex() + "'," + user.getMoney() + ",'" + user.getName() + "','" + user.getIdNO() + "'," + user.getNameAuth() + ",'手动统一导入'),");
            }
            stringBuffer.delete(stringBuffer.length() - 1, stringBuffer.length());
            stringBuffer.append(";");
            objectList.add(stringBuffer.toString());
        }
        FileUtils.witeList(objectList, "C:\\Users\\Administrator\\Desktop\\导入用户.txt");
    }

    public static void main(String[] args) throws FileNotFoundException {

    /*List<JSONObject> objects =read1("C:\\Users\\Administrator\\Desktop\\数据汇总(1).xls");
    for (JSONObject jsonobject:objects) {
      File file = new File("C:\\Users\\Administrator\\Desktop\\4.txt");
      FileOutputStream fos1 = new FileOutputStream(file, true);
      // 创建FileOutputStream对应的PrintStream，方便操作。PrintStream的写入接口更便利
      PrintStream out1 = new PrintStream(fos1);
      String lonString =jsonobject.getString("lon");
      String latString =jsonobject.getString("lat");
      JSONObject address= getAddress(Double.parseDouble(lonString),Double.parseDouble(latString));
      String s= address.getJSONObject("result").getJSONObject("address_component").getString("city");
      String xian= address.getJSONObject("result").getJSONObject("address_component").getString("district");
      String province= address.getJSONObject("result").getJSONObject("address_component").getString("province");
      System.out.println(s);
      String url ="https://baike.baidu.com/item/"+xian;
      String result="";
      if(xian!=null){
         result= HttpClientUtils.httpGet(url,null);
      }
      String man="";
      if(result.contains("常住人口")){
        Integer i= result.indexOf("常住人口");
        man=isNumber(result.substring(i,i+100));
      }
      String mile="";
      if(result.contains("总面积")){
        Integer i= result.indexOf("面积");
        mile=isNumber1(result.substring(i,i+100));
      }
      String str=jsonobject.getString("accountId")+"/"+province+":"+s+","+man+";"+mile+"\r\n";
      //String str=jsonobject.getString("accountId")+"/"+province+":"+s+"\r\n";
      out1.print(str);
      out1.close();
    }*/
    /*String url ="https://baike.baidu.com/item/深圳/140588?fromtitle=深圳市&fromid=11044365&fr=aladdin";
    String result= HttpClientUtils.httpGet(url,null);
    if(result.contains("常住人口")){
      Integer i= result.indexOf("常住人口");
      String s=result.substring(i,i+30);
      System.out.println(s);
      System.out.println(isNumber(s));
    }
    if(result.contains("总面积")){
      Integer i= result.indexOf("总面积");
      String s=result.substring(i,i+20);
      System.out.println(s);
      System.out.println(isNumber1(s));
    }*/
    /*JSONObject address= getAddress(113.94460615909,22.5684233636363);
    String s= address.getJSONObject("result").getJSONObject("address_component").getString("city");
    System.out.println(address);
    System.out.println(s);*/

    }

    /**
     * 维持单session
     */
    public static HttpClient getClient() {
        if (client == null) {
            MultiThreadedHttpConnectionManager connectionManager = new MultiThreadedHttpConnectionManager();
            client = new HttpClient(connectionManager);
            /**设置编码*/
            client.getParams().setContentCharset("UTF-8");
            /**请求超时*/
            client.getHttpConnectionManager().getParams().setConnectionTimeout(1000);
            /**读取超时*/
            client.getHttpConnectionManager().getParams().setSoTimeout(5000);
        }
        return client;
    }


    /**
     * 查询地址
     */
    public static JSONObject getAddress(Double lo, Double ln) {
        HttpClient client = getClient();
        GetMethod get = null;
        String address = null;
        try {
            get = new GetMethod("https://apis.map.qq.com/ws/geocoder/v1/?get_poi=0&key=EIJBZ-NQ2LF-UU6JA-JP27O-CNOZ2-E6FZZ&location=" + ln + "," + lo);
            client.executeMethod(get);
            String result = get.getResponseBodyAsString();
            JSONObject object = JSONObject.parseObject(result);
            return object;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            get.releaseConnection();
        }

        log.info(lo + "," + ln + "=" + address);

        return null;
    }

    public static String isNumber(String str) {
        StringBuffer tmp = new StringBuffer();
        Boolean isNum = false;
        for (int i = 0; i < str.length(); i++) {
            if ((str.charAt(i)) == '.') {
                tmp.append(str.charAt(i));
            }
            if (isNum && (str.charAt(i)) == '人') {
                return tmp.toString();
            }
            if (Character.isDigit(str.charAt(i))) {
                tmp.append(str.charAt(i));
                isNum = true;
            }

        }
        return tmp.toString();
    }

    public static String isNumber1(String str) {
        StringBuffer tmp = new StringBuffer();
        Boolean isNum = false;
        for (int i = 0; i < str.length(); i++) {
            if ((str.charAt(i)) == '.') {
                tmp.append(str.charAt(i));
            }
            if (isNum && (str.charAt(i)) == '米') {
                return tmp.toString();
            }
            if (Character.isDigit(str.charAt(i))) {
                tmp.append(str.charAt(i));
                isNum = true;
            }

        }
        return tmp.toString();
    }


}
